﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace DAL {
    public class LabSer {
       
        public int SearchTable( string UserName ) {
            SqlConnection conn = ConnPoll.GetConn();
                try {
                string sql = string.Format("select count(1) from Labels{0}", UserName);
                SqlCommand com = new SqlCommand(sql, conn);
                ConnPoll.Open();
                int i = Convert.ToInt32(com.ExecuteScalar());
                conn.Close();
                return i;
            }
            catch (Exception) {
                ConnPoll.Close();
                return -1;
            }
        }

        #region 创建标签表
        /// <summary>
        /// 创建标签表
        /// </summary>
        /// <param name="UserName"></param>
        public void CreateLable( string ChildName ) {
            SqlConnection conn = ConnPoll.GetConn();
            try {
                string tbna = "Labels" + ChildName;
                string sql = "use bds249611391_db "
                    + " create table " + tbna
                    + " ( "
                    + " Id int not null identity(1,1)," //主键Id
                    + " FriendName nvarchar(Max) not null,"  //好友名称
                    + " LabelsText nvarchar(Max) not null" //标签内容
                    + " ) ";
                SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql);
            }
            catch (Exception) {
                throw;
            }
        }
        #endregion

        #region 新增信息
        /// <summary>
        /// 新增信息
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Insert( string UserName, string FriendName, string LabelsText ) {
            SqlConnection conn = ConnPoll.GetConn();
            if (SearchLabelsText(UserName, LabelsText) > 0)
                return 0;
            string sql = "insert into Labels" + UserName + " (FriendName,LabelsText)" + " values" + " (@FriendName,@LabelsTexts)";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@FriendName",FriendName),
                new SqlParameter("@LabelsTexts", LabelsText)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);
        }
        #endregion

        #region 删除信息
        /// <summary>
        /// 删除信息
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Delete( string UserName, string FriendName, string LabelsText ) {
            SqlConnection conn = ConnPoll.GetConn();
            StringBuilder sb = new StringBuilder();
            List<string> L = SearchAll(UserName, FriendName);
            for (int i = 0; i < L.Count; i++) {
                if (L[i] == "" || L[i] == LabelsText)
                    continue;
                if (sb.ToString() == "")
                    sb.Append(L[i]);
                else
                    sb.Append("/" + L[i]);
            }
            string sql = string.Format("update Labels" + UserName + " set LabelsText='{0}' where FriendName='{1}'", sb.ToString(), FriendName);
            SqlCommand com = new SqlCommand(sql, conn);
            try {
                ConnPoll.Open();
                int i = com.ExecuteNonQuery();
                com.Dispose();
                ConnPoll.Close();
                return i;
            }
            catch (Exception) {
                com.Dispose();
                ConnPoll.Close();
                return -1;
            }
        }
        #endregion

        #region 修改信息
        public int Set( string UserName, string FriendName, string LabelsText ) {
            SqlConnection conn = ConnPoll.GetConn();
            StringBuilder sb = new StringBuilder();
            List<string> L = SearchAll(UserName, FriendName);
            for (int i = 0; i < L.Count; i++) {
                if (L[i] == "")
                    continue;
                if (sb.ToString() == "")
                    sb.Append(L[i]);
                else
                    sb.Append("/" + L[i]);
            }
            if (sb.ToString().Contains(LabelsText)) {
                //MessageBox.Show("标签已存在");
                return -1;
            }
            if (sb.ToString() != "")
                sb.Append("/" + LabelsText);
            else
                sb.Append(LabelsText);
            string sql = "update Labels" + UserName
                + " set LabelsText=@LabelsText"
                + " where FriendName=@FriendName";
            SqlParameter[] pars = new SqlParameter[]
            {
                new SqlParameter("@LabelsText",sb.ToString()),
                new SqlParameter("@FriendName",FriendName)
            };
            return SQLHelper.ExecuteNonQuery(conn, System.Data.CommandType.Text, sql, pars);
        }
        #endregion

        #region SearchLabelsText
        private int SearchLabelsText( string ChildName, string LabelsText ) {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select Count(1) From Labels{0} where LabelsText like '%{1}%'", ChildName, LabelsText);
            return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql));
        }
        #endregion

        #region 查询单人标签
        public List<string> SearchAll( string UserName, string FriendName ) {
            SqlConnection conn = ConnPoll.GetConn();
            List<string> L = new List<string>();
            string sql = "select LabelsText from Labels" + UserName + " where FriendName='" + FriendName + "'";
            string s = SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql).ToString();
            if (s.Contains("/")) {
                do {
                    int i = s.IndexOf("/");
                    L.Add(s.Substring(0, i));
                    s = s.Substring(i + 1);
                } while (s.Contains("/"));
                L.Add(s);
            }
            else
                L.Add(s);
            return L;
        }
        #endregion

        #region 查询所有标签
        public List<string> SearchFriend( string UserName ) {
            SqlConnection conn = ConnPoll.GetConn();
            List<string> L = new List<string>();
            string sql = string.Format("select LabelsText from Labels{0}", UserName);
            SqlDataReader dr = SQLHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
            while (dr.Read()) {
                string str = dr["LabelsText"].ToString();
                if (str.Contains("/")) {
                    do {
                        int i = str.IndexOf("/");
                        string s = str.Substring(0, i);
                        if (!L.Contains(s))
                            L.Add(str.Substring(0, i));
                        str = str.Substring(i + 1);
                    } while (str.Contains("/"));
                    if (!L.Contains(str))
                        L.Add(str);
                }
                else {
                    if (str != "") {
                        if (!L.Contains(str))
                            L.Add(str);
                    }
                }
            }
            dr.Close();
            return L;
        }
        #endregion

        #region 查找库中是否已有此内容
        /// <summary>
        /// 查找库中是否有表 
        /// </summary>
        /// <param name="l"></param>
        /// <param name="UserName"></param>
        /// <returns></returns>
        public int Search( string UserName, string FriendName ) {
            SqlConnection conn = ConnPoll.GetConn();
            string sql = "select count(1) from Labels" + UserName + " where FriendName=@FriendName";
            try {
                return Convert.ToInt32(SQLHelper.ExecuteScalar(conn, System.Data.CommandType.Text, sql, new SqlParameter("@FriendName", FriendName)));
            }
            catch (Exception) {
                return -1;
                throw;
            }
        }
        #endregion

        #region 通过标签查询好友昵称
        public List<string> SearchFriendNick( string UserName, string LabelsText ) {
            List<string> L = new List<string>();
            SqlConnection conn = ConnPoll.GetConn();
            string sql = string.Format("select FriendName from Labels{0} where LabelsText like '%{1}%'", UserName, LabelsText);
            SqlDataReader dr = SQLHelper.ExecuteReader(conn, System.Data.CommandType.Text, sql);
            while (dr.Read()) {
                L.Add(dr["FriendName"].ToString());
            }
            dr.Close();
            return L;
        }
        #endregion
    }
}
